package db;


public class MySqlNativeStatements extends AbstractDbNativeStatements {
	
	
	
	
	public String getAllColumnsSql(String schema, String table) {
		return " SELECT column_name AS COLUMN_NAME, data_type AS DATA_TYPE , character_maximum_length AS DATA_LENGTH, column_comment AS column_comment, IS_NULLABLE AS NULLABLE " +
					" FROM information_schema.columns " +
					" WHERE table_schema = '" + schema + "' AND table_name = '" + table + "'";
	}

	public String getAllTablesSql(String schema) {
		return " SELECT table_name AS TABLE_NAME " +
		 " FROM information_schema.tables " +
		 " WHERE table_schema = '" + schema + "'";
	}

	@Override
	public String getPrimaryKeyColumns(String schema, String table) {
		return "SELECT column_name AS COLUMN_NAME  " +
				" FROM   information_schema.key_column_usage " +
				" WHERE  table_schema = '" + schema + "' AND table_name = '" + table + "' AND constraint_name = 'PRIMARY'";
	}

	@Override
	public String getReferencesSql(String schema, String table) {
		return "	SELECT k.table_schema AS TABLE_SCHEMA, k.TABLE_NAME AS TABLE_NAME, k.COLUMN_NAME AS COLUMN_NAME, " +
				"           c.data_type AS DATA_TYPE, " +
				"	       k.referenced_table_name AS R_TABLE_NAME, k.referenced_table_schema AS R_TABLE_SCHEMA, k.referenced_table_name AS R_TABLE_SCHEMA, k.referenced_column_name AS R_COLUMN_NAME, " +
				"               cr.data_type AS R_DATA_TYPE " +
				"	FROM   information_schema.key_column_usage k " +
				"        LEFT OUTER JOIN information_schema.columns c " +
				"         ON c.table_schema = k.table_schema AND c.table_name = k.table_name AND c.column_name = k.column_name " +
				"        LEFT OUTER JOIN information_schema.columns cr " +
				"        ON cr.table_schema = k.referenced_table_schema AND cr.table_name = k.referenced_table_name AND cr.column_name = k.referenced_column_name " +
				"	WHERE  k.table_schema = '" + schema + "' AND k.table_name = '" + table + "' AND k.referenced_table_name IS NOT NULL ";  
		
	}

	@Override
	public String getIndexesSql(String schema, String table) {
		return " SELECT s.index_name AS INDEX_NAME, s.column_name AS COLUMN_NAME,  " + 
				" 		c.data_type AS DATA_TYPE, (CASE c.column_key WHEN 'PRI' THEN 'UNIQUE' WHEN 'UNI' THEN 'UNIQUE' ELSE 'NONUNIQUE' END) AS UNIQUENESS " +
				"		FROM information_schema.statistics s, information_schema.columns c " +
				"		WHERE s.table_schema = '" + schema + "' " +
				"		        AND s.table_name = '" + table + "' " +
				"	            AND s.table_schema = c.table_schema " +
				"		        AND s.table_name = c.table_name " +
				"		        AND s.column_name = c.column_name" +
				"       GROUP BY 2,3,4";
	}

}
